﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyDuAn.DTO;
using QuanLyDuAn.DataAccess;
using System.Collections;

namespace QuanLyDuAn.BUS
{
    class InterviewerController
    {
        DataSet ds;
        ArrayList list = new ArrayList();
        ArrayList list1 = new ArrayList();
        SqlCommand cmd;
        SqlDataAdapter da;
        SqlConnection conn;
        SqlDataReader reader,reader1;
        Interviewer interviewer = new Interviewer();
        dbAccess database = new dbAccess();
        public DataSet LoadInterviewer()
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("GetAllInterviewer", conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interviewer");
            return ds;
        }
        public void InsertInterviewer(String id,String username,String password, String FullName,String Department,int pid)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("InsertInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@iUsername", SqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@iPassword", SqlDbType.VarChar).Value = password;
            cmd.Parameters.Add("@iFullname", SqlDbType.VarChar).Value = FullName;
            cmd.Parameters.Add("@iDepartment", SqlDbType.VarChar).Value = Department;
            cmd.Parameters.Add("@pid", SqlDbType.VarChar).Value = pid;
            int i = cmd.ExecuteNonQuery();
        }
        public void UpdateInterviewer(String id, String username, String password, String FullName, String Department, int pid)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd= new SqlCommand("UpdateInterviewer",conn);
            cmd.CommandType= CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = id;
            cmd.Parameters.Add("@iUsername", SqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@iPassword", SqlDbType.VarChar).Value = password;
            cmd.Parameters.Add("@iFullname", SqlDbType.VarChar).Value = FullName;
            cmd.Parameters.Add("@iDepartment", SqlDbType.VarChar).Value = Department;
            cmd.Parameters.Add("@pid", SqlDbType.VarChar).Value = pid;
            int i = cmd.ExecuteNonQuery();
            

        }
        public void DelInterviewer(String id)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("DelInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = id;
            int i = cmd.ExecuteNonQuery();


        }
        public void GetInterviewr_Username(String username)
        {

            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getInterviewr_Username", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iUsername", SqlDbType.VarChar).Value = username;
            da.Fill(ds);
            da.Dispose();
        }
        public ArrayList LoginInterviewer()
        {
            Interviewer interviewer;
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("GetAllInterviewer", conn);
            da = new SqlDataAdapter(cmd);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String iid = reader.GetString(0);
                String username = reader.GetString(1);
                String password = reader.GetString(2);
                String fullName = reader.GetString(3);
                String department = reader.GetString(4);
                int permission = reader.GetInt32(5);
                interviewer = new Interviewer(iid, username, password, fullName, department, permission);
                list.Add(interviewer);
            }
            return list;
        }
        public ArrayList GetInterviewerByUser(String username)
        {
            Interviewer interviewer1;
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getInterviewerByUser", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            da = new SqlDataAdapter(cmd);
            reader1 = cmd.ExecuteReader();
            while (reader1.Read())
            {
                String iid = reader1.GetString(0);
                String userName = reader1.GetString(1);
                String password = reader1.GetString(2);
                String fullName = reader1.GetString(3);
                String department = reader1.GetString(4);
                int permission = reader1.GetInt32(5);
                interviewer1 = new Interviewer(iid, userName, password, fullName, department, permission);
                list1.Add(interviewer1);
            }
            return list1;
        }
        public void ChangePassInterviewer(String usename, String password)
        {
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("chagePassInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = usename;
            cmd.Parameters.Add("@iPassword", SqlDbType.VarChar).Value = password;
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public DataSet SearchInterviewByName(String name)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchInterviewByName", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iFullname", SqlDbType.VarChar).Value = name;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interviewer");
            return ds;
        }
        public DataSet SearchInterviewByDepartment(String department)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("searchInterviewByDepartment", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iDepartment", SqlDbType.VarChar).Value = department;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interviewer");
            return ds;
        }
        public ArrayList GetInterviewerName()
        {
            ArrayList list = new ArrayList();
            conn = database.GetConnect();
            conn.Open();
            cmd = new SqlCommand("getNameInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                String name = reader.GetString(0);
                list.Add(name);
            }
            return list;
        }
        public DataSet CheckInterviewer(String iId)
        {
            conn = database.GetConnect();
            conn.Open();
            ds = new DataSet();
            cmd = new SqlCommand("CheckInterviewer", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@iId", SqlDbType.VarChar).Value = iId;
            da = new SqlDataAdapter(cmd);
            da.Fill(ds, "Interviewer");
            return ds;
        }
    }
}
